package cn.jhc.db;

import cn.jhc.beans.SurveyItem;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SurveyItemDao {
    private static final String INSERT_SQL = "insert into survey(" +
            "name, age, gender, occupation, hobbies) values(?, ?, ?, ?, ?)";
    private static final String SELECT_SQL = "select id, name, age, gender, occupation, hobbies from survey";
    private static final String DELETE_SQL = "delete from survey where id = ?";
    private static final String SELECT_BY_ID_SQL = "select id, name, age, gender, occupation, hobbies from survey where id = ?";
    private static final String UPDATE_SQL = "update survey set name = ?, age = ?, gender = ?, occupation = ?, hobbies = ? where id = ?";
    private static final String COUNT_SQL = "select count(*) as count from survey";
    private static final String FIND_BY_PAGE_SQL = "select id, name, age, gender, occupation, hobbies from survey limit ? offset ?";

    public static void save(Connection connection, SurveyItem item) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SQL);
            preparedStatement.setString(1, item.getName());
            preparedStatement.setInt(2, item.getAge());
            preparedStatement.setString(3, item.getGender());
            preparedStatement.setString(4, item.getOccupation());
            String hobbies = "";
            for (String hobby : item.getHobbies()) {
                hobbies += hobby + ",";
            }
            preparedStatement.setString(5, hobbies);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static List<SurveyItem> list(Connection connection) {
        List<SurveyItem> items = new ArrayList<>();
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(SELECT_SQL);
            while (resultSet.next()) {
                SurveyItem item = new SurveyItem();
                item.setId(resultSet.getInt("id"));
                item.setName(resultSet.getString("name"));
                item.setAge(resultSet.getInt("age"));
                item.setGender(resultSet.getString("gender"));
                item.setOccupation(resultSet.getString("occupation"));
                item.setHobbies(resultSet.getString("hobbies").split(","));
                items.add(item);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return items;
    }
    
    public static void delete(Connection connection, int id) {
        try {
            PreparedStatement statement = connection.prepareStatement(DELETE_SQL);
            statement.setInt(1, id);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static SurveyItem findById(Connection connection, int id) {
        try {
            PreparedStatement statement = connection.prepareStatement(SELECT_BY_ID_SQL);
            statement.setInt(1, id);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                SurveyItem item = new SurveyItem();
                item.setId(resultSet.getInt("id"));
                item.setName(resultSet.getString("name"));
                item.setAge(resultSet.getInt("age"));
                item.setGender(resultSet.getString("gender"));
                item.setOccupation(resultSet.getString("occupation"));
                item.setHobbies(resultSet.getString("hobbies").split(","));
                return item;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void updateById(Connection connection, SurveyItem form) {
        try {
            PreparedStatement statement = connection.prepareStatement(UPDATE_SQL);
            statement.setString(1, form.getName());
            statement.setInt(2, form.getAge());
            statement.setString(3, form.getGender());
            statement.setString(4, form.getOccupation());
            statement.setString(5, String.join(",", form.getHobbies()));
            statement.setInt(6, form.getId());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static int count(Connection connection) {
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(COUNT_SQL);
            if (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    
    public static void batchInsert(Connection connection) {
        List<SurveyItem> items = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            SurveyItem item = new SurveyItem();
            item.setName("姓名" + i);
            item.setAge(20 + i % 10);
            item.setGender(i % 2 == 0 ? "male" : "female");
            item.setOccupation("engineer");
            item.setHobbies(new String[]{"Sports", "Music"});
            items.add(item);
        }
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SQL);
            for (int i = 0; i < items.size(); i++) {
                SurveyItem item = items.get(i);
                preparedStatement.setString(1, item.getName());
                preparedStatement.setInt(2, item.getAge());
                preparedStatement.setString(3, item.getGender());
                preparedStatement.setString(4, item.getOccupation());
                preparedStatement.setString(5, String.join(",", item.getHobbies()));
                preparedStatement.addBatch();
                if (i % 100 == 0) {
                    preparedStatement.executeBatch();
                }
            }
            preparedStatement.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<SurveyItem> findByPage(Connection connection, int offset, int pageSize) {
        List<SurveyItem> items = new ArrayList<>();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_BY_PAGE_SQL);
            preparedStatement.setInt(2, offset);
            preparedStatement.setInt(1, pageSize);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                SurveyItem item = new SurveyItem();
                item.setId(resultSet.getInt("id"));
                item.setName(resultSet.getString("name"));
                item.setAge(resultSet.getInt("age"));
                item.setGender(resultSet.getString("gender"));
                item.setOccupation(resultSet.getString("occupation"));
                item.setHobbies(resultSet.getString("hobbies").split(","));
                items.add(item);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return items;
    }
}
